﻿using System;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;

namespace App_Code
{
    /// <summary>
    /// Summary description for GuestHouseDB
    /// </summary>
    public static class GuestHouseDB
    {
        static readonly string _connStr = String.Format("server={0};uid={1};pwd={2};database={3};Allow User Variables=True;",
                                                     "anotherurl.com", "root", "rootpw!", "laostraveller");
        /*         
         create table gh(gh_id int NOT NULL AUTO_INCREMENT, 
         ip varchar(100) NOT NULL, dt datetime NOT NULL, location varchar(100) not null,
         report varchar(2000) not null, hidden boolean not null,
         PRIMARY KEY (gh_id));
         */

        public static int SubmitReport(string ip, GuestHouseRecord record)
        {
            int id = -1;
            string dt = String.Format("{0:yyyy-M-dd HH:mm:ss}", DateTime.UtcNow);

            string temp = record.Record.Replace("\"", "'");

            try
            {
                string sql = "INSERT INTO gh(ip, dt, location, report, hidden) VALUES(\"" + ip + "\", \"" + dt + 
                    "\", \"" + record.Location +
                    "\", \"" + temp + "\", \"0\");";

                const string sql1 = "select LAST_INSERT_ID();";

                using (var conn = new MySqlConnection(_connStr))
                {
                    conn.Open();

                    MySqlTransaction myTrans = conn.BeginTransaction();

                    using (var cmd = new MySqlCommand(sql, conn, myTrans))
                    {
                       cmd.ExecuteScalar();
                    }

                    using (var cmd1 = new MySqlCommand(sql1, conn, myTrans))
                    {
                        id = (int)(long)cmd1.ExecuteScalar();
                    }

                    myTrans.Commit();
                    conn.Close();
                }
            }
            catch (Exception)
            {
            }
            return id;
        }

        public static void HideReport(int id)
        {
            try
            {
                string sql = "UPDATE gh set hidden = 1 where gh_id = " + id + ";";

                using (var conn = new MySqlConnection(_connStr))
                {
                    conn.Open();

                    using (var cmd = new MySqlCommand(sql, conn))
                    {
                        cmd.ExecuteScalar();
                    }

                    conn.Close();
                }
            }
            catch (Exception)
            {
            }
        }

        public static int UpdateReport(string ip, GuestHouseRecord record, int id)
        {
            HideReport(id);
            return SubmitReport(ip, record);
        }

        internal static void GetReports(Dictionary<int, GuestHouseRecord> records)
        {
            try
            {
                using (var conn = new MySqlConnection(_connStr))
                {
                    conn.Open();

                    const string sql = "select gh_id, report, location from gh where hidden = 0;";

                    using (var cmd = new MySqlCommand(sql, conn))
                    {
                        var myReader = cmd.ExecuteReader();
                        var ds = new DataSet();
                        ds.Load(myReader, LoadOption.PreserveChanges, new[] {"TABLE"});
                        myReader.Close();

                        foreach (DataRow row in ds.Tables["TABLE"].Rows)
                        {
                            records.Add((int)row[0], new GuestHouseRecord((string)row[1], (string)row[2]));
                        }
                    }
                }
            }
            catch(Exception )
            {
            }
        }
    }
}